<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
 <head>
  <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  <title>Multiple Statements</title>

 </head>
 <body><div class="manualnavbar" style="text-align: center;">
 <div class="prev" style="text-align: left; float: left;"><a href="mysqli.quickstart.stored-procedures.html">Stored Procedures</a></div>
 <div class="next" style="text-align: right; float: right;"><a href="mysqli.quickstart.transactions.html">API support for transactions</a></div>
 <div class="up"><a href="mysqli.quickstart.html">Quick start guide</a></div>
 <div class="home"><a href="index.html">PHP Manual</a></div>
</div><hr /><div id="mysqli.quickstart.multiple-statement" class="section">
  <h2 class="title">Multiple Statements</h2>
  <p class="para">
   MySQL optionally allows having multiple statements in one statement string.
   Sending multiple statements at once reduces client-server
   round trips but requires special handling.
  </p>
  <p class="para">
   Multiple statements or multi queries must be executed
   with <span class="function"><a href="mysqli.multi-query.html" class="function">mysqli_multi_query()</a></span>. The individual statements
   of the statement string are separated by semicolon.
   Then, all result sets returned by the executed statements must be fetched.
  </p>
  <p class="para">
   The MySQL server allows having statements that do return result sets and
   statements that do not return result sets in one multiple statement.
  </p>
  <p class="para">
   <div class="example" id="example-1611">
    <p><strong>Example #1 Multiple Statements</strong></p>
    <div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB">&lt;?php<br />$mysqli&nbsp;</span><span style="color: #007700">=&nbsp;new&nbsp;</span><span style="color: #0000BB">mysqli</span><span style="color: #007700">(</span><span style="color: #DD0000">"example.com"</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">"user"</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">"password"</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">"database"</span><span style="color: #007700">);<br />if&nbsp;(</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">connect_errno</span><span style="color: #007700">)&nbsp;{<br />&nbsp;&nbsp;&nbsp;&nbsp;echo&nbsp;</span><span style="color: #DD0000">"Failed&nbsp;to&nbsp;connect&nbsp;to&nbsp;MySQL:&nbsp;("&nbsp;</span><span style="color: #007700">.&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">connect_errno&nbsp;</span><span style="color: #007700">.&nbsp;</span><span style="color: #DD0000">")&nbsp;"&nbsp;</span><span style="color: #007700">.&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">connect_error</span><span style="color: #007700">;<br />}<br /><br />if&nbsp;(!</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"DROP&nbsp;TABLE&nbsp;IF&nbsp;EXISTS&nbsp;test"</span><span style="color: #007700">)&nbsp;||&nbsp;!</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"CREATE&nbsp;TABLE&nbsp;test(id&nbsp;INT)"</span><span style="color: #007700">))&nbsp;{<br />&nbsp;&nbsp;&nbsp;&nbsp;echo&nbsp;</span><span style="color: #DD0000">"Table&nbsp;creation&nbsp;failed:&nbsp;("&nbsp;</span><span style="color: #007700">.&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">errno&nbsp;</span><span style="color: #007700">.&nbsp;</span><span style="color: #DD0000">")&nbsp;"&nbsp;</span><span style="color: #007700">.&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">error</span><span style="color: #007700">;<br />}<br /><br /></span><span style="color: #0000BB">$sql&nbsp;</span><span style="color: #007700">=&nbsp;</span><span style="color: #DD0000">"SELECT&nbsp;COUNT(*)&nbsp;AS&nbsp;_num&nbsp;FROM&nbsp;test;&nbsp;"</span><span style="color: #007700">;<br /></span><span style="color: #0000BB">$sql</span><span style="color: #007700">.=&nbsp;</span><span style="color: #DD0000">"INSERT&nbsp;INTO&nbsp;test(id)&nbsp;VALUES&nbsp;(1);&nbsp;"</span><span style="color: #007700">;<br /></span><span style="color: #0000BB">$sql</span><span style="color: #007700">.=&nbsp;</span><span style="color: #DD0000">"SELECT&nbsp;COUNT(*)&nbsp;AS&nbsp;_num&nbsp;FROM&nbsp;test;&nbsp;"</span><span style="color: #007700">;<br /><br />if&nbsp;(!</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">multi_query</span><span style="color: #007700">(</span><span style="color: #0000BB">$sql</span><span style="color: #007700">))&nbsp;{<br />&nbsp;&nbsp;&nbsp;&nbsp;echo&nbsp;</span><span style="color: #DD0000">"Multi&nbsp;query&nbsp;failed:&nbsp;("&nbsp;</span><span style="color: #007700">.&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">errno&nbsp;</span><span style="color: #007700">.&nbsp;</span><span style="color: #DD0000">")&nbsp;"&nbsp;</span><span style="color: #007700">.&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">error</span><span style="color: #007700">;<br />}<br /><br />do&nbsp;{<br />&nbsp;&nbsp;&nbsp;&nbsp;if&nbsp;(</span><span style="color: #0000BB">$res&nbsp;</span><span style="color: #007700">=&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">store_result</span><span style="color: #007700">())&nbsp;{<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color: #0000BB">var_dump</span><span style="color: #007700">(</span><span style="color: #0000BB">$res</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">fetch_all</span><span style="color: #007700">(</span><span style="color: #0000BB">MYSQLI_ASSOC</span><span style="color: #007700">));<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color: #0000BB">$res</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">free</span><span style="color: #007700">();<br />&nbsp;&nbsp;&nbsp;&nbsp;}<br />}&nbsp;while&nbsp;(</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">more_results</span><span style="color: #007700">()&nbsp;&amp;&amp;&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">next_result</span><span style="color: #007700">());<br /></span><span style="color: #0000BB">?&gt;</span>
</span>
</code></div>
    </div>

    <div class="example-contents"><p>以上例程会输出：</p></div>
    <div class="example-contents screen">
<div class="cdata"><pre>
array(1) {
  [0]=&gt;
  array(1) {
    [&quot;_num&quot;]=&gt;
    string(1) &quot;0&quot;
  }
}
array(1) {
  [0]=&gt;
  array(1) {
    [&quot;_num&quot;]=&gt;
    string(1) &quot;1&quot;
  }
}
</pre></div>
    </div>
   </div>
  </p>
  <p class="para">
   <em class="emphasis">Security considerations</em>
  </p>
  <p class="para">
   The API functions <span class="function"><a href="mysqli.query.html" class="function">mysqli_query()</a></span> and
   <span class="function"><a href="mysqli.real-query.html" class="function">mysqli_real_query()</a></span> do not set a connection flag necessary
   for activating multi queries in the server. An extra API call is used for
   multiple statements to reduce the likeliness of accidental SQL injection
   attacks. An attacker may try to add statements such as
   <em>; DROP DATABASE mysql</em> or <em>; SELECT SLEEP(999)</em>.
   If the attacker succeeds in adding SQL to the statement string but
   <em>mysqli_multi_query</em> is not used, the server will not
   execute the second, injected and malicious SQL statement.
  </p>
  <p class="para">
   <div class="example" id="example-1612">
    <p><strong>Example #2 SQL Injection</strong></p>
    <div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB">&lt;?php<br />$mysqli&nbsp;</span><span style="color: #007700">=&nbsp;new&nbsp;</span><span style="color: #0000BB">mysqli</span><span style="color: #007700">(</span><span style="color: #DD0000">"example.com"</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">"user"</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">"password"</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">"database"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$res&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color: #007700">=&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"SELECT&nbsp;1;&nbsp;DROP&nbsp;TABLE&nbsp;mysql.user"</span><span style="color: #007700">);<br />if&nbsp;(!</span><span style="color: #0000BB">$res</span><span style="color: #007700">)&nbsp;{<br />&nbsp;&nbsp;&nbsp;&nbsp;echo&nbsp;</span><span style="color: #DD0000">"Error&nbsp;executing&nbsp;query:&nbsp;("&nbsp;</span><span style="color: #007700">.&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">errno&nbsp;</span><span style="color: #007700">.&nbsp;</span><span style="color: #DD0000">")&nbsp;"&nbsp;</span><span style="color: #007700">.&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">error</span><span style="color: #007700">;<br />}<br /></span><span style="color: #0000BB">?&gt;</span>
</span>
</code></div>
    </div>

    <div class="example-contents"><p>以上例程会输出：</p></div>
    <div class="example-contents screen">
<div class="cdata"><pre>
Error executing query: (1064) You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax 
to use near &#039;DROP TABLE mysql.user&#039; at line 1
</pre></div>
    </div>
   </div>
  </p>
  <p class="para">
   <em class="emphasis">Prepared statements</em>
  </p>
  <p class="para">
   Use of the multiple statement with prepared statements is not supported.
  </p>
  <p class="para">
   <em class="emphasis">See also</em>
  </p>
  <p class="para">
   <ul class="simplelist">
    <li class="member"><span class="methodname"><a href="mysqli.query.html" class="methodname">mysqli::query()</a></span></li>
    <li class="member"><span class="methodname"><a href="mysqli.multi-query.html" class="methodname">mysqli::multi_query()</a></span></li>
    <li class="member"><span class="methodname"><strong>mysqli_result::next-result()</strong></span></li>
    <li class="member"><span class="methodname"><strong>mysqli_result::more-results()</strong></span></li>
   </ul>
  </p>
 </div><hr /><div class="manualnavbar" style="text-align: center;">
 <div class="prev" style="text-align: left; float: left;"><a href="mysqli.quickstart.stored-procedures.html">Stored Procedures</a></div>
 <div class="next" style="text-align: right; float: right;"><a href="mysqli.quickstart.transactions.html">API support for transactions</a></div>
 <div class="up"><a href="mysqli.quickstart.html">Quick start guide</a></div>
 <div class="home"><a href="index.html">PHP Manual</a></div>
</div></body></html>
